if object_id (N'[dbo].[add_new_personnel]', N'P') IS NOT NULL
    drop procedure [dbo].[add_new_personnel];
go

/********************************************************
creator: Zoran Arsovski
created: 03.11.2007.
last modifier: unknown
last modified: unknown

description: Procedurata dodava nov red vo tabelata Personnel
			 dokolku istiot ne postoi.
			 Vo sprotivno vrsi update na podatocite.
test: add_new_personnel '1203980450666', 'Tester', 'Tester', 'test', 'test123', null, null, null, null,
												 null, '2007.11.03', null
*********************************************************/

create procedure [dbo].[add_new_personnel] 
(
	-- Input Parametars person
	@embg varchar(20),
	@first_name nvarchar(30),
	@last_name nvarchar(50),
	@username nvarchar(30),
	@password nvarchar(30),
	@address nvarchar(50) = null,
	@city nvarchar(30) = null,   
	@birth_date datetime = null,
	@birth_place nvarchar(30) = null,
	-- Input Parametars personnel
	@department_id bigint = null,
	@entry_date datetime = null,
	@spec_type_id int = null
) as
begin

	begin transaction
	/*** Vo samata procedura se proveruva ako ne postoi @embg togas se pravi insert ***
	 *** vo sprotivno se pravi update na podatocite vo Person tabelata.							***/
	exec add_new_person @embg, @first_name, @last_name, @username, @password, @address, @city, @birth_date, @birth_place

	if not exists (select 1 from personnel where embg = @embg)
		insert into personnel
		(embg, department_id, entry_date, spec_type_id)
		values
		(@embg, @department_id, @entry_date, @spec_type_id)
	else
		update personnel
		set department_id = @department_id,
				entry_date		= @entry_date,
				spec_type_id  = @spec_type_id
		where embg = @embg

	if @@error <> 0    
		begin
			rollback
			return    
		end 
	else
		begin
			commit
			return    
		end 

end
go		